﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using LumenWorks.Framework.IO.Csv;

namespace FileTranslator
{
    // sqlite导入csv文件
    internal static class CsvHelper
    {
        public static OleDbConnection OleCon = new OleDbConnection();
        public static OleDbCommand OleCmd = new OleDbCommand();
        public static OleDbDataAdapter OleDa = new OleDbDataAdapter();
        public static DataSet CsvData = new DataSet();

        public static void CsvToLocalTbl(string filePath, ref DataTable csvTable)
        {
            // 打开"data.csv" ，这个csv文件应该有一个标题行
            using (CsvReader csv =
                new CsvReader(new StreamReader(filePath, Encoding.Default, true), true))
            {
                //字段数量
                int fieldCount = csv.FieldCount;

                //标题数组
                string[] headers = csv.GetFieldHeaders();

                // 装载列
                foreach (string header in headers)
                {
                    csvTable.Columns.Add(new DataColumn(header, typeof(string)));
                }
         
                //只进的游标读取
                while (csv.ReadNextRecord())
                {
                    //遍历列
                    DataRow dr = csvTable.NewRow();
                    for (int i = 0; i < fieldCount; i++)
                    {
                        dr[headers[i]] = csv[i];
                        //Console.Write(string.Format("{0} = {1};",headers[i], csv[i]));
                    }
                    csvTable.Rows.Add(dr);
                    //Console.WriteLine();
                }
            }
            CsvData.Tables.Add(csvTable);
        }

        public static void LocalTblToCsv(DataTable dt, string filePath)
        {
            FileStream fs = new FileStream(filePath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
            string data = "";

            //写出列名称
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                data += dt.Columns[i].ColumnName.ToString();
                if (i < dt.Columns.Count - 1)
                {
                    data += ",";
                }
            }
            sw.WriteLine(data);

            //写出各行数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                data = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    data += dt.Rows[i][j].ToString();
                    if (j < dt.Columns.Count - 1)
                    {
                        data += ",";
                    }
                }
                sw.WriteLine(data);
            }

            sw.Close();
            fs.Close();
        }
    }  
}




